 1.条件函数【重要】
   
   -- if (boolean testCondition, T valueTrue, T valueFalseOrNull)
select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp;
   -- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
   -- 将emp表的员工工资等级分类：0-1500、1500-3000、3000以上
select empno, ename, deptno, sal, 
       if (sal<=1500, 1, if (sal <= 3000, 2, 3)) sallevel from
emp;
   -- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
   -- 复杂条件用 case when 更直观
select empno, ename, deptno, sal, 
      case when sal<=1500 then 1
           when sal<=3000 then 2
           else 3 end sallevel
from emp;
   -- 以下语句等价
select empno, ename, deptno,
       case deptno when 10 then 'accounting'
            when 20 then 'research'
            when 30 then 'sales'
            else 'unknown' end deptname
from emp;
   select empno, ename, deptno,
          case when deptno=10 then 'accounting'
               when deptno=20 then 'research'
               when deptno=30 then 'sales'
               else 'unknown' end deptname
from emp; 
   - COALESCE(T v1, T v2, ...)。返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
   select sal, coalesce(comm, 0) from emp;
   -- isnull(a) isnotnull(a)
   select * from emp where isnull(comm);
   select * from emp where comm is null;
   select * from emp where isnotnull(comm);
   select * from emp where comm is not null;
   -- nvl(T value, T default_value)
select empno, ename, job, mgr, hiredate, deptno, sal +
       nvl(comm,0) sumsal
from emp;
   -- nullif(x, y) 相等为空，否则为a
SELECT nullif("b", "b"), nullif("b", "a");
 
 2.UDTF函数【重要】
   
   UDTF : User Defined Table-Generating Functions。用户定义表生成函数，一行输入，多行输出。
   -- explode，炸裂函数
   -- 就是将一行中复杂的 array 或者 map 结构拆分成多行
   select explode(array('A','B','C')) as col;
   select explode(map('a', 8, 'b', 88, 'c', 888));
   -- UDTF's are not supported outside the SELECT clause, nor nested in expressions
   -- SELECT pageid, explode(adid_list) AS myCol... is not supported
   -- SELECT explode(explode(adid_list)) AS myCol... is not supported
   -- lateral view 常与 表生成函数explode结合使用
   -- lateral view 语法：
lateralView: LATERAL VIEW udtf(expression) tableAlias AS
      columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
   -- lateral view 的基本使用
   with t1 as (
   select 'OK' cola, split('www.lagou.com', '\\.') colb
)
select cola, colc
    from t1
lateral view explode(colb) t2 as colc;
 
 3.UDTF 案例1：
   
   -- 数据(id tags)：
1	1,2,3
2	2,3
3	1,2
   --编写sql,实现如下结果：
1 1
1 2
1 3
2 2
2 3
3 1
3 2
   
   -- 建表加载数据
   create table tab1(id int, tags string)
row format delimited fields terminated by '\t';
   load data local inpath '/home/hadoop/data/tab1.dat' into table tab1;
   -- SQL
select id, split(tags, ',')
   from tab1;
select id, tag
   from tab1
lateral view explode(split(tags, ",")) t1 as tag;
 
 4.UDTF 案例2
   
   -- 数据准备
lisi|Chinese:90,Math:80,English:70
wangwu|Chinese:88,Math:90,English:96
maliu|Chinese:99,Math:65,English:60
   
   -- 创建表
create table studscore(
  name string
 ,score map<String,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':';
   -- 加载数据
  load data local inpath '/home/hadoop/data/score.dat' overwrite into table studscore;
  -- 需求：找到每个学员的最好成绩
  -- 第一步，使用 explode 函数将map结构拆分为多行
  select explode(score) as (subject, socre) from studscore;
  --但是这里缺少了学员姓名，加上学员姓名后出错。下面的语句有是错的
select name, explode(score) as (subject, socre) from
studscore;
  -- 第二步：explode常与 lateral view 函数联用，这两个函数结合在一起能关联其他字段
  select name, subject, score1 as score from studscore
lateral view explode(score) t1 as subject, score1;
  -- 第三步：找到每个学员的最好成绩
select name, max(mark) maxscore
      from (select name, subject, mark
            from studscore lateral view explode(score) t1 as
             subject, mark) t1
group by name;
  
  with tmp as (
select name, subject, mark
from studscore lateral view explode(score) t1 as subject,
mark
)
select name, max(mark) maxscore
from tmp
group by name;
  
  小结：
  将一行数据转换成多行数据，可以用于array和map类型的数据；
  lateral view 与 explode 联用，解决 UDTF 不能添加额外列的问题